package com.mz3co.sysadmin.service.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.mz3co.sysadmin.dao.SysUsersExMapper;
import com.mz3co.sysadmin.dao.SysUsersMapper;
import com.mz3co.sysadmin.service.utils.CommonUtils;
import com.mz3co.sysadmin.model.SysUsers;
import com.mz3co.sysadmin.model.SysUsersExample;
import com.mz3co.sysadmin.model.SysUsersExample.Criteria;
import com.mz3co.sysadmin.model.ex.SysUsersEx;
import com.mz3co.sysadmin.page.Page;
import com.mz3co.sysadmin.service.SysAuthenticationInfoService;
import com.mz3co.sysadmin.service.SysUsersRolesService;
import com.mz3co.sysadmin.service.SysUsersService;

public class SysUsersServiceImpl implements SysUsersService {

	@Autowired
	private SysUsersMapper usersMapper;
	
	@Autowired
	private SysUsersExMapper usersExMapper;

	@Autowired
	private SysUsersRolesService usersRolesService;

	@Autowired
	private DataSource dataSource;

	@Autowired
	private SysAuthenticationInfoService authenticationInfoService;

	public Map<String,Object> getHttpUsers(Integer pageSize,Integer pageIndex,SysUsers user) {

		// 获取包含员工或客户信息的用户对象
		/*
		 * select
		 * id,username,createtime,status,last_login_time,last_login_ip,type
		 * ,user_detail_id, case when type ='员工' then (SELECT employee_name from
		 * employee where employee_id = user_detail_id) when type = '客户'then
		 * (SELECT customer_name from customer where customer_id =
		 * user_detail_id) end user_detail_name from users
		 * 
		 * select u.id
		 * id,username,createtime,status,last_login_time,last_login_ip
		 * ,type,e.employee_id detail_id, e.employee_name name from users u LEFT
		 * JOIN employee e on u.user_detail_id = e.employee_id where type = '员工'
		 * or type is NULL UNION select u.id
		 * id,username,createtime,status,last_login_time
		 * ,last_login_ip,type,c.customer_id detail_id, c.customer_name name
		 * from users u LEFT JOIN customer c on u.user_detail_id = c.customer_id
		 * where type = '客户'
		 */
		
		SysUsersExample example=new SysUsersExample();
		Criteria criteria = example.createCriteria();
		
		List<String> typeList = new ArrayList<String>();
		 if(user.getType() == null ){
			typeList.add("员工");
			typeList.add("司机");
			typeList.add("嘉宾");
			typeList.add("客商");
			//typeList.add("客商");
			
			
		}else{
			String[] types = user.getType().split(",");
			for(String type:types)
				typeList.add(type);
			
			
		}
		 criteria.andTypeIn(typeList);//criteria.andTypeEqualTo(user.getType());
			
		
		if (!StringUtils.isEmpty(user.getUsername())) {
			criteria.andUsernameLike("%" +user.getUsername() + "%");
		}
		
		int totalrecords=usersMapper.countByExample(example);
		/**
		 * for my sql db
		 */
			Page page = new Page();
			
			page.setBegin((pageIndex-1)*pageSize);
			page.setLength(pageSize);
			
			example.setPage(page);
			
			List<SysUsers> list = usersMapper.selectByExample(example);
			
		/**
		 * for  sql server db
		 */
//		example.setPageIndex(pageIndex);
//		example.setPageSize(pageSize);
//	
//		example.setOrderByClause("id desc");
//		
//		List<SysUsers> list=usersMapper.selectPage(example);
		
		if(list == null || list.size() <1){
			
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("total", totalrecords);
			map.put("rows",list);
			return map;
		}
		
		StringBuffer sb=new StringBuffer();
		sb.append('(');
		for(int i=0;i<list.size()-1;i++){
			sb.append(list.get(i).getId());
			sb.append(',');
		}
		sb.append(list.get(list.size()-1).getId());
		sb.append(')');
		
		
		Connection conn = null;
		Statement stmt = null;
		
		
		ResultSet rs = null;
		
		
		List<SysUsersEx> listHttpUsers = new ArrayList<SysUsersEx>();

		try {
			conn = dataSource.getConnection();

			stmt = conn.createStatement();

			// 按照起运港，目的港，分组获取推荐的报价
			String sql = "select  u.\"id\" id,\"username\",\"createtime\",\"last_login_time\",\"last_login_ip\",\"type\",\"user_detail_id\",c.client_name detail_name"
					+ " from sys_users u LEFT JOIN client c on u.\"user_detail_id\" = c.client_id "
					+ " where (\"type\" =  '员工' or \"type\" =  '司机' or \"type\" = '客商' or \"type\" = '嘉宾' or \"type\" is NULL ) and u.\"id\" in "+ sb;
			

			rs = stmt.executeQuery(sql);

			this.RestuSetToListHttpUsers(conn,rs, listHttpUsers);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			try {
				rs.close();
				stmt.close();
				conn.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("total", totalrecords);
		map.put("rows",listHttpUsers);

		return map;
		
	}


	
	private String getRoleNames(Connection conn,Integer userId) throws SQLException{
		
		
//		select * 
//		from users_roles u,roles r
//		where u.rid = r.id and uid=1
		
		String roleName = "";
		String sql = "select \"rid\",\"name\"  from sys_users_roles u,sys_roles r where u.\"rid\" = r.\"id\" and \"uid\"="+userId;
		
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		
		while (rs.next()) {
			
			roleName += rs.getString("name") +"  ";
		}
		
		rs.close();
		stmt.close();
		
		return roleName;
	}
	private void RestuSetToListHttpUsers(Connection conn,ResultSet rs, List<SysUsersEx> listHttpUsers)
			throws SQLException {
		
		
		Integer userId = 0;

		while (rs.next()) {

			SysUsersEx hu = new SysUsersEx();
			
			userId = rs.getInt("id");
			hu.setId(new BigDecimal(userId));
			hu.setUsername(rs.getString("username"));
			hu.setsCreateTime(rs.getString("createtime"));
//			hu.setStatus(rs.getString("status"));
			hu.setsLastLoginTime(rs.getString("last_login_time"));
			hu.setLastLoginIp(rs.getString("last_login_ip"));
			hu.setType(rs.getString("type"));
			hu.setUserDetailId(new BigDecimal(rs.getInt("user_detail_id")));
			hu.setUserDetailName(rs.getString("detail_name"));
			
			hu.setRoleNames(this.getRoleNames(conn, userId));

			
			listHttpUsers.add(hu);
		}

	}

	public List<SysUsers> getUsers() {

		return usersMapper.selectByExample(null);
	}

	public SysUsersServiceImpl() {
		// TODO Auto-generated constructor stub
	}

	@Override
	public boolean isExistUsers(Integer roleId) {
		// TODO Auto-generated method stub
		return true;
	}

	@Override
	public boolean insert(SysUsers user) {

		// TODO Auto-generated method stub
		return usersExMapper.insertSelectiveAndGetId(user) == 1 ? true : false;
	}

	@Override
	@Transactional
	public boolean delete(String ids) {


		SysUsersExample example = new SysUsersExample();
		List<BigDecimal> listD=new ArrayList<BigDecimal>();
		for(Integer i:CommonUtils.idsArrayToList(ids)){
			listD.add(new BigDecimal(i));
		}
		example.or().andIdIn(listD);
		
		List<SysUsers> userList = usersMapper.selectByExample(example);
		
		
		try{
			
			for(SysUsers user:userList){
				
				// 将用户定义的角色删除 ,将分配该用户的认证信息删除
				if(!usersRolesService.delete(user.getId().toString(),"") ||
							!authenticationInfoService.deleteByUserId(user.getId().intValue()))
					return false;
				
			}
			usersMapper.deleteByExample(example) ;
			
			
		}catch(Exception e){
			e.printStackTrace();
			
			return false;
			
		}

		return true;
	}

	@Override
	public boolean update(SysUsers user) {
		// TODO Auto-generated method stub
		return usersMapper.updateByPrimaryKeySelective(user) == 1 ? true : false;
	}



	@Override
	public SysUsers getUsers(Integer userId) {
		// TODO Auto-generated method stub
		return usersMapper.selectByPrimaryKey(new BigDecimal(userId));
	}



}
